package cn.bdqn.dao;

import cn.bdqn.entity.Customer;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;

/**
 * JpaRepository:代表增删改查的基本操作方法，封装的都是最基本的方法  //对应要操作的实体类和实体类的ID类型
 * JpaSpecificationExecutor：代表复杂的查询，例如：分页       //对应实体类
 */
public interface CustomerDao extends JpaRepository<Customer,Long>,JpaSpecificationExecutor<Customer> {

    //使用jpql语句操作
    //********************************************************

    /**
     * @Query:默认使用jpql语句进行查询
     * 查询全部信息
     * @return
     */
    @Query(value = "from Customer")
    public List<Customer> findAll();

    /**
     * 根据客户名称进行条件查询
     * sql：select from cst_customer where cst_name = ?
     * jpql:from Customer where cusName = ?
     */
    @Query(value = "from Customer where custName = ?")
    public Customer findByNameJpql(String name);

    /**
     * 需求：根据客户名称和客户地址模糊查询
     * sql：select * from cust_customer where cust_name like ? and cust_address = ?
     * jpql:from Customer where custName like ？ custAddress = ?
     * jpql赋值顺序默认是按照参数顺序排序的（可在占位符后加入对应参数在方法中的位置索引）
     */
    @Query(value = "from Customer where custName like ?1 and custAddress = ?2")
    public Customer findByNameAndAddress(String name,String address);

    /**
     * 需求：通过id修改客户
     * sql：update cst_customer set cust_name = ? where cust_id = ?
     * jpql:update Customer set custName = ? where custId = ?
     */
    @Query(value = "UPDATE  Customer set custName = ? where custId = ?")
    @Modifying
    @Transactional
    public void updatejpql(String name,Long id);

    /**
     * 需求：使用sql语句根据客户名称，地址进行模糊查询
     * sql:select  * cst_customer where cust_name like ? and cust_address = ?
     */
    @Query(value = "select * from cst_customer where cust_name like ?1 and cust_address = ?2",nativeQuery = true)//官方不推荐
    public List<Customer> findAllSql(String name,String address);


    //使用命名规范查询
    //******************************************************************************

    /**
     * 需求：柑橘客户名称查询客户
     * 命名规范前缀：findBy+类的首字母大写
     */
    public  Customer findByCustName(String name);

    /**
     * 需求：通过客户名称和客户地址进行模糊查询
     * 命名规范：findByCustNameLikeAndCustAddressLike()
     */
    public List<Customer> findByCustNameLikeAndCustAddressLike(String name,String address);

















}
